﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;

namespace bitMail.Class
{
    internal class SqliteHelper
    {    
        public string LiteDbName
        {
            get
            {
                return "data.db";
            }
        }

        public string AbsolutePath
        {
            get
            {
                return Path.Combine(CurrentPath, LiteDbName);
            }
        }

        public string CurrentPath
        {
            get
            {
                var directoryInfo = (new FileInfo(System.Reflection.Assembly.GetEntryAssembly().Location)).Directory;
                return directoryInfo != null ? directoryInfo.ToString() : "";
            }
        }

        static string _createTableQuery = @"CREATE TABLE IF NOT EXISTS [Szablony] (
                              [ID_Szablonu] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                              [NazwaSzablonu] NVARCHAR(200)  NULL,
                              [TrescSzablonu] VARCHAR(2048)  NULL                       
                              );
                            CREATE TABLE IF NOT EXISTS [Daily] (
                              [DailyId] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                              [DailyDate] TEXT  NULL,
                              [DailyText] VARCHAR(2048)  NULL
                              )
                            ";

        public DataSet ReutrnDataSet()
        {
            var ds4 = new DataSet();
            try
            {
                const string query = "SELECT ID_Szablonu, NazwaSzablonu, TrescSzablonu, TematMaila FROM Szablony";
                using (var con = new SQLiteConnection("data source=" + AbsolutePath))
                {
                    if (con.State != ConnectionState.Open)
                    {
                        con.Open();
                    }
                    using (new SQLiteCommand(con))
                    {
                        var connect4 = new SQLiteDataAdapter(query, con);
                        connect4.Fill(ds4);
                        con.Close();
                    }
                }
                return ds4;
            }
            catch
            {
                return ds4;
            }
        }

        public Template GetTemplateById(int id)
        {
            var result = new Template();
            try
            {
                var query = "SELECT ID_Szablonu, NazwaSzablonu, TrescSzablonu FROM Szablony WHERE ID_Szablonu = " + id.ToString();
                using (var con = new SQLiteConnection("data source=" + AbsolutePath))
                {
                    if (con.State != ConnectionState.Open)
                    {
                        con.Open();
                    }
                    using (var com = new SQLiteCommand(con))
                    {
                        com.CommandText = query;
                        var reader = com.ExecuteReader();
                        while (reader.Read())
                        {
                            result.TemplateId = int.Parse(reader["ID_Szablonu"].ToString());
                            result.TemplateName = reader["NazwaSzablonu"].ToString();
                            result.TemplateText = reader["TrescSzablonu"].ToString();
                        }
                        con.Close();
                    }
                }
                return result;
            }
            catch
            {
                return result;
            }
        }

        public bool Update(Template template)
        {
            try
            {
                var query = "UPDATE  Szablony SET NazwaSzablonu = '" + template.TemplateName +"'," +
                                " TrescSzablonu = '" + template.TemplateText + "'" +                                
                                " WHERE ID_Szablonu =" + template.TemplateId.ToString();

                using (var con = new SQLiteConnection("data source=" + AbsolutePath))
                {
                    if (con.State != ConnectionState.Open)
                    {
                        con.Open();
                    }
                    using (var com = new SQLiteCommand(con))
                    {
                        com.CommandText = query;
                        com.ExecuteNonQuery();
                    }
                    con.Close();
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool Delete(int id)
        {
            try
            {
                var query = "DELETE FROM Szablony WHERE ID_Szablonu =" + id;
                using (var con = new SQLiteConnection("data source=" + AbsolutePath))
                {
                    if (con.State != ConnectionState.Open)
                    {
                        con.Open();
                    }
                    using (var com = new SQLiteCommand(con))
                    {
                        com.CommandText = query;
                        com.ExecuteNonQuery();
                    }
                    con.Close();
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool CreateDbAndTableIfNotExist()
        {
            try
            {
                if (File.Exists(Path.Combine(CurrentPath, LiteDbName)))
                {
                    return true;
                }
                else
                {
                    SQLiteConnection.CreateFile(LiteDbName);
                    using (var con = new SQLiteConnection("data source=" + AbsolutePath))
                    {
                        using (var com = new SQLiteCommand(con))
                        {
                            con.Open();
                            com.CommandText = _createTableQuery;
                            com.ExecuteNonQuery();
                            con.Close();
                        }
                    }
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool AddNewTemplate(string name, string text)
        {
            try
            {
                var query = "insert into Szablony (NazwaSzablonu, TrescSzablonu) values ('" + name + "','" + text + "')";
                using (var con = new SQLiteConnection("data source=" + AbsolutePath))
                {
                    if (con.State != ConnectionState.Open)
                    {
                        con.Open();
                    }
                    using (var com = new SQLiteCommand(con))
                    {
                        com.CommandText = query;
                        com.ExecuteNonQuery();
                    }
                    con.Close();
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        public List<Daily> GetDailyEntries(DateTime data)
        {
            var dat = ConvertDateToliteString(data);
            var result = new List<Daily>();
            try
            {
                var query = "SELECT DailyId, DailyDate, DailyText FROM Daily WHERE DailyDate = '" + dat + "'";
                using (var con = new SQLiteConnection("data source=" + AbsolutePath))
                {
                    if (con.State != ConnectionState.Open)
                    {
                        con.Open();
                    }
                    using (var com = new SQLiteCommand(con))
                    {
                        com.CommandText = query;
                        var reader = com.ExecuteReader();
                        while (reader.Read())
                        {
                            result.Add(new Daily
                            {
                                DailyId = int.Parse(reader["DailyId"].ToString()),
                                DailyDate = reader["DailyDate"].ToString(),
                                DailyText = reader["DailyText"].ToString()
                            });
                        }
                        con.Close();
                    }
                }
                return result;
            }
            catch
            {
                return result;
            }
        }

        public string ConvertDateToliteString(DateTime data)
        {
            return data.Year.ToString() + "-" + data.Month.ToString() + "-" + data.Day.ToString();
        }

        public bool SaveNewDailyEntry(string text, DateTime data)
        {
            try
            {
                var dat = ConvertDateToliteString(data);
                var query = "insert into Daily (DailyText, DailyDate) values ('" + text + "','" + dat + "')";
                using (var con = new SQLiteConnection("data source=" + AbsolutePath))
                {
                    if (con.State != ConnectionState.Open)
                    {
                        con.Open();
                    }
                    using (var com = new SQLiteCommand(con))
                    {
                        com.CommandText = query;
                        com.ExecuteNonQuery();
                    }
                    con.Close();
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        public string GetSubjectBasedOnBodyId(int templateId)
        {
            var result = "";
            try
            {
                var query = "SELECT NazwaSzablonu  FROM Szablony WHERE  ID_Szablonu = " + templateId.ToString();
                using (var con = new SQLiteConnection("data source=" + AbsolutePath))
                {
                    if (con.State != ConnectionState.Open)
                    {
                        con.Open();
                    }
                    using (var com = new SQLiteCommand(con))
                    {
                        com.CommandText = query;
                        var reader = com.ExecuteReader();
                        while (reader.Read())
                        {
                            result = reader["NazwaSzablonu"].ToString();
                        }
                        con.Close();
                    }
                }
                return result;
            }
            catch
            {
                return result;
            }
        }

        public List<Template> GetAllTemplates()
        {
            var result = new List<Template>();
            try
            {
                var query = "SELECT ID_Szablonu, NazwaSzablonu, TrescSzablonu FROM Szablony";
                using (var con = new SQLiteConnection("data source=" + AbsolutePath))
                {
                    if (con.State != ConnectionState.Open)
                    {
                        con.Open();
                    }
                    using (var com = new SQLiteCommand(con))
                    {
                        com.CommandText = query;
                        var reader = com.ExecuteReader();
                        while (reader.Read())
                        {
                            result.Add(new Template
                            {
                                TemplateId = int.Parse(reader["ID_Szablonu"].ToString()),
                                TemplateName = reader["NazwaSzablonu"].ToString(),
                                TemplateText = reader["TrescSzablonu"].ToString()
                            });
                        }
                        con.Close();
                    }
                }
                return result;
            }
            catch
            {
                return result;
            }
        }
    }
}
